﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_User.cs
    // 文件功能描述：用户数据访问逻辑
    // 创建标识：庄金冬2009-03-06
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using System.Data;
using System.Data.SqlClient;
using HZ.Common;

namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 用户数据访问逻辑
    /// </summary>
    public class DAL_User
    {
        /// <summary>
        /// 获取所有用户（不含权限）
        /// </summary>
        private static string sql_GetAllUser = "SELECT * FROM TB_User";




        /// <summary>
        /// 根据编号获取用户（不包含用户信息，角色信息，权限信息，机构信息）
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static User GetUserByUserID(string userid)
        {

            // 根据UserID获取单个用户（不包含用户信息，角色信息，权限信息，机构信息）
            string sql_GetUserByUserID = "SELECT * FROM TB_User WHERE rownum<=1 and UserID = @UserID";
            //SqlParameter[] paras = new SqlParameter[] {
            //    new SqlParameter("@UserID",string.IsNullOrEmpty(userid)?"":userid)
            //};
            //DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql_GetUserByUserID, paras);

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql_GetUserByUserID, HZ.Data.DBParameterFactory.GetIDbDataParameter("@UserID", string.IsNullOrEmpty(userid) ? "" : userid));


            if (dt.Rows.Count > 0)
            {
                //return HZ.Data.DAL_Helper.GetUserFromDataRow(dt.Rows[0]);
                return HZ.Data.DAL_Helper.CommonFill<User>(dt.Rows[0]);
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 根据编号获取用户（包含用户信息，角色信息，权限信息，机构信息）
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static User GetUserInfoByUserID(string userid)
        {
            /*
            User user = new User();
            try
            {
                SqlParameter[] paras = new SqlParameter[1];

                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

                DataSet ds = new DataSet();
                ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_GetUserInfoByUserID", paras);

                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        //user = HZ.Data.DAL_Helper.GetUserFromDataRow(ds.Tables[0].Rows[0]);
                        //Role role = HZ.Data.DAL_Helper.GetRoleFromDataRow(ds.Tables[1].Rows[0]);
                        //user.UserRole = role;

                        //Org org = HZ.Data.DAL_Helper.GetOrgFromDataRow(ds.Tables[2].Rows[0]);
                        //user.Org = org;

                        //IList<OperatingRule> operatingRuleList = HZ.Data.DAL_Helper.GetOperatingRulesFromDataTable(ds.Tables[3]);

                        user = HZ.Data.DAL_Helper.CommonFill<User>(ds.Tables[0].Rows[0]);
                        Role role = HZ.Data.DAL_Helper.CommonFill<Role>(ds.Tables[1].Rows[0]);
                        user.UserRole = role;

                        Org org = HZ.Data.DAL_Helper.CommonFill<Org>(ds.Tables[2].Rows[0]);
                        user.Org = org;

                        IList<OperatingRule> operatingRuleList = HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(ds.Tables[3]);
                        user.OperatingRuleList = (List<OperatingRule>)operatingRuleList;
                    }
                }

            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "根据编号获取用户（包含用户信息，角色信息，权限信息，机构信息）不成功", ex);
#if DEBUG
                throw ex;
#endif
            }

            return user;
             * */
            User user = new User();
            try
            {
                var h = HZ.Data.DbHelperFactory.Create();
                if (int.Parse(h.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TB_User WHERE  UserID = '{0}'", userid)).ToString()) > 0)
                {
                    DataSet ds = new DataSet();
                    // ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_GetUserInfoByUserID", paras);

                    user = HZ.Data.DAL_Helper.CommonFill<User>(h.ExecuteTable(string.Format("SELECT * FROM  TB_User WHERE  UserID = '{0}'", userid)).Rows[0]);
                    DataRow dr = h.ExecuteTable(string.Format("SELECT RoleID,RoleName, IsDefault, IsAdmin,OrgID, UserID FROM View_RoleUser WHERE  UserID = '{0}'", userid)).Rows[0];
                    string strOrgID = dr["OrgID"].ToString();
                    Role role = HZ.Data.DAL_Helper.CommonFill<Role>(dr);
                    user.UserRole = role;

                    Org org = new Org();
                    //user.Org = org;
                    switch (role.RoleID)
                    {
                        case "01"://医疗机构
                            org = HZ.Data.DAL_Helper.CommonFill<Org>(h.ExecuteTable(string.Format("SELECT HospitalID  OrgID,AreaID, HospitalName  OrgName FROM   TB_Hospital WHERE  HospitalID = '{0}'", strOrgID)).Rows[0]);
                            break;
                        case "02"://配送企业
                            org = HZ.Data.DAL_Helper.CommonFill<Org>(h.ExecuteTable(string.Format("SELECT CompanyID   OrgID,CompanyName  OrgName  FROM   TB_Company WHERE  CompanyID = '{0}'", strOrgID)).Rows[0]);

                            break;
                        case "05"://生产企业
                            org = HZ.Data.DAL_Helper.CommonFill<Org>(h.ExecuteTable(string.Format("SELECT CompanyID  OrgID,CompanyName  OrgName FROM TB_Company WHERE  CompanyID = '{0}'", strOrgID)).Rows[0]);
                            break;
                        case "03"://中心
                            org = HZ.Data.DAL_Helper.CommonFill<Org>(h.ExecuteTable(string.Format("SELECT ManageOrgID  OrgID, ManageOrgName  OrgName FROM   TB_ManageOrg WHERE  ManageOrgID = '{0}'", strOrgID)).Rows[0]);
                            break;
                    }
                    user.Org = org;
                    IList<OperatingRule> operatingRuleList = HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(h.ExecuteTable(string.Format(@" SELECT OperatingRuleID ,OperatingRuleName,Module,ComFlag,CenFlag,HosFlag,UserID ,IsEffective FROM  View_UserRule WHERE  UserID = '{0}'", userid)));
                    user.OperatingRuleList = (List<OperatingRule>)operatingRuleList;
                }
                else
                {
                    user = null;
                }

            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "根据编号获取用户（包含用户信息，角色信息，权限信息，机构信息）不成功", ex);
#if DEBUG
                throw ex;
#endif
            }


            return user;


        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>用户列表</returns>
        public static IList<User> GetAllUser()
        {
            IList<User> list;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_GetAllUser);
            //list = HZ.Data.DAL_Helper.GetUsersFromDataTable(dt);
            list = HZ.Data.DAL_Helper.CommonFillList<User>(dt);
            return list;
        }


        /// <summary>
        /// 用户登录0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="userpassword">用户密码</param>
        /// <param name="user">(out)用户实例</param>
        /// <returns>0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;</returns>
        public static int Login(string userid, string userpassword, out User user)
        {
            user = new User();
            int retValue = 0;
            try
            {
                retValue = uspLogin(userid, userpassword, out user);
            }
            catch (Exception ex)
            {
                throw ex;
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "添加用户并设置权限不成功", ex);

              
            }

            return retValue;

        }

        private static int uspLogin(string userid, string userpassword, out User user)
        {
            user = new User();
            HZ.Data.IDbHelper h = HZ.Data.DbHelperFactory.Create();
            string sql1 = @"SELECT * FROM TB_User WHERE  upper(UserID) = upper(@UserID)";
            DataTable userdt = null;
            userdt = h.ExecuteTable(CommandType.Text, sql1, HZ.Data.DBParameterFactory.GetIDbDataParameter("@UserID", userid.Trim()));

            if (userdt.Rows.Count == 0)
            {
                // 不存在改用户
                user = null;
                return 2;

            }
            else
            {
                string pwd = userdt.Rows[0]["UserPassword"].ToString();

                if (string.Compare(userpassword, pwd, true) != 0) // 密码不对
                {
                    user = null;
                    return 2;
                }
                else
                {
                    string isusing = userdt.Rows[0]["IsUsing"].ToString();

                    if (isusing.ToUpper() != "1" && isusing.ToUpper() != "TRUE") // 已经启用
                    {
                        user = null;
                        return 3;
                    }

                }
            }

            // 更新登录时间
            //string sqlupdate = @"UPDATE TB_User SET IsOnline = 1, LastLoginTime = '{0}'  WHERE  UserID = '{1}'";
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format(sqlupdate, DateTime.Now.ToString(), userid)); // todo 确定oracle日期格式


            //OracleParameter[] paras = new OracleParameter[]
            //{
            //    new OracleParameter(":LastLoginTime", DateTime.Now),
            //    new OracleParameter(":UserID", userid)
            //};

            List<string> parasnames = new List<string>();
            List<object> values = new List<object>();

            parasnames.Add("@LastLoginTime");
            values.Add(DateTime.Now);

            parasnames.Add("@UserID");
            values.Add(userid);


            //HZ.Data.DBParameterFactory.GetIDbDataParameterList(

            // string sqlupdate = @"UPDATE TB_User SET IsOnline = 1 WHERE  UserID =:UserID";
            //string sqlupdate = @"UPDATE TB_User SET IsOnline = 1 , LastLoginTime =:LastLoginTime WHERE  UserID =:UserID";
            string sqlupdate = @"UPDATE TB_User SET IsOnline = 1 , LastLoginTime =@LastLoginTime WHERE  upper(UserID) = upper(@UserID)";
            //
            string aaa = h.ExecuteNonQuery(CommandType.Text, sqlupdate, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray()).ToString();
            // HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format(sqlupdate, DateTime.Now.ToString(), userid)); // todo 确定oracle日期格式


            var roledt = h.ExecuteTable(CommandType.Text, @"SELECT RoleID,RoleName,IsDefault,IsAdmin,OrgID,UserID FROM View_RoleUser WHERE upper(UserID) = upper(@UserID)", HZ.Data.DBParameterFactory.GetIDbDataParameter("@UserID", userid.Trim())); // 角色信息
            var permissiondt = h.ExecuteTable(CommandType.Text, @"SELECT * FROM View_UserRule WHERE  upper(UserID) = upper(@UserID)", HZ.Data.DBParameterFactory.GetIDbDataParameter("@UserID", userid.Trim())); // 权限表
            string roleid = roledt.Rows[0]["RoleID"].ToString();
            string orgid = roledt.Rows[0]["OrgID"].ToString();

            string sql = "";
            DataTable orgdt = null;
            switch (roleid)
            {
                case "02":
                case "05":
                    sql = "SELECT CompanyID OrgID, CompanyName OrgName, SNKEY FROM  TB_Company  WHERE  upper(CompanyID) = upper(@OrgId)";

                    break;
                case "01": //医疗机构
                    sql = "SELECT HospitalID OrgID, AreaID ,HospitalName OrgName,SNKEY FROM TB_Hospital WHERE upper(HospitalID) = upper(@OrgId)";
                    break;

                default: // 中心机构

                    sql = @"SELECT ManageOrgID OrgID, AreaID,  ManageOrgName OrgName, '' SNKEY FROM  TB_ManageOrg WHERE upper(ManageOrgID) = upper(@OrgId)";
                    break;
            }

            orgdt = h.ExecuteTable(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@OrgId", orgid));
            DataSet ds = new DataSet();

            userdt.TableName = "userdt";
            roledt.TableName = "roledt";
            orgdt.TableName = "orgdt";
            permissiondt.TableName = "permissiondt";

            ds.Tables.Add(userdt.Copy());
            ds.Tables.Add(roledt.Copy());
            ds.Tables.Add(orgdt.Copy());
            ds.Tables.Add(permissiondt.Copy());
            #region 改版
            //SqlParameter[] paras = new SqlParameter[]
            //    {
            //         //TB_User【UserID,UserName,UserPassword,IsAdmin,IsOnline,IsOnline,IsUsing】
            //         //用户编号
            //        new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid),
            //         //密码
            //        new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(userpassword) ? "" : userpassword),
            //        //输入参数处理结果(0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;)
            //        new SqlParameter("@out_success", SqlDbType.Int)
            //    };

            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //DataSet ds = new DataSet();
            //ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_Login", paras);

            #endregion
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    #region 改版
                    //user = HZ.Data.DAL_Helper.GetUserFromDataRow(ds.Tables[0].Rows[0]);
                    //Role role = HZ.Data.DAL_Helper.GetRoleFromDataRow(ds.Tables[1].Rows[0]);
                    //user.UserRole = role;

                    //Org org = HZ.Data.DAL_Helper.GetOrgFromDataRow(ds.Tables[2].Rows[0]);
                    //user.Org = org; 
                    #endregion


                    //user = HZ.Data.DAL_Helper.CommonFill<User>(ds.Tables[0].Rows[0]);
                    //Role role = HZ.Data.DAL_Helper.CommonFill<Role>(ds.Tables[1].Rows[0]);
                    //user.UserRole = role;

                    //Org org = HZ.Data.DAL_Helper.CommonFill<Org>(ds.Tables[2].Rows[0]);
                    //user.Org = org;

                    user = HZ.Data.DAL_Helper.CommonFill<User>(userdt.Rows[0]);
                    Role role = HZ.Data.DAL_Helper.CommonFill<Role>(roledt.Rows[0]);
                    user.UserRole = role;

                    Org org = HZ.Data.DAL_Helper.CommonFill<Org>(orgdt.Rows[0]);
                    Area area = new Area();
                    if (roleid != "05" && roleid != "02")
                    {
                        area.AreaID = orgdt.Rows[0]["AreaID"].ToString();
                        org.Area = area;
                    }

                    org.Area = area;
                    user.Org = org;

                    List<OperatingRule> OperatingRuleList = (List<OperatingRule>)HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(permissiondt);
                    user.OperatingRuleList = OperatingRuleList;


                }
            }

            //return int.Parse(paras[2].Value.ToString());

            return 1;
        }

        /// <summary>
        /// 用户登录0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="userpassword">用户密码</param>
        /// <param name="RoleID">用户角色</param>
        /// <param name="user">(out)用户实例</param>
        /// <returns>0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;</returns>
        public static int Login(string userid, string userpassword, string RoleID, out User user)
        {
            user = new User();
            int retValue = 0;
            string strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID<>'01'  AND  RoleID<>'02'  AND UserID='{0}' ";
            switch (RoleID.Trim())
            {
                //医疗机构
                case "01":
                    strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID='01' AND UserID='{0}' ";
                    break;
                //配送企业
                case "02":
                    strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID='02' AND UserID='{0}' ";
                    break;
                default:
                    break;
            }

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(strTemp, userid));
            if (dt.Rows.Count <= 0)
            {
                return 0;
            }

            try
            {
                retValue = uspLogin(userid, userpassword, out user);
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "用户登陆不成功", ex);
            }

            return retValue;

        }

        /// <summary>
        /// 更新用户信息[名称、是否启用](0,更新失败;1,更新成功;2,用户编号不存在;)
        /// </summary>
        /// <param name="user">用户[名称、是否启用]</param>
        /// <param name="Flag">0更新信息，1锁定/解锁</param>
        /// <returns>(0,更新失败;1,更新成功;2,用户编号不存在;)</returns>
        public static int UpdateUser(User user, int Flag)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[5];

                //TB_User【UserID,UserName,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID);
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName);
                //是否启用
                paras[2] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);
                //标志：0更新信息，1锁定/解锁
                paras[3] = new SqlParameter("@in_Flag", Flag);


                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[4].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_Update", paras);

                retValue = int.Parse(paras[4].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户信息用户不成功", ex);

            }
            return retValue;
            */
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                // SELECT * FROM TB_User WHERE  UserID = @in_UserID
                string IsExit = string.Format("SELECT count(*) FROM TB_User WHERE  UserID = '{0}'", user.UserID);
                if (int.Parse(h.ExecuteScalar(IsExit).ToString()) > 0)
                {
                    List<string> UpdateUserparasnames = new List<string>();
                    List<object> UpdateUservalues = new List<object>();

                    UpdateUserparasnames.Add("@in_UserID");
                    UpdateUservalues.Add(string.IsNullOrEmpty(user.UserID) ? "" : user.UserID.Trim());

                    string sqlUpdateUserIsUsing = @" UPDATE TB_User SET  IsUsing = @in_IsUsing WHERE  UserID = @in_UserID";
                    string sqlUpdateUserName = @"UPDATE TB_User SET  UserName = @in_UserName WHERE  UserID = @in_UserID";
                    if (Flag == 1)
                    {
                        UpdateUserparasnames.Add("@in_IsUsing");
                        UpdateUservalues.Add(user.IsUsing ? 1 : 0);
                        h.ExecuteNonQuery(CommandType.Text, sqlUpdateUserIsUsing, HZ.Data.DBParameterFactory.GetIDbDataParameterList(UpdateUserparasnames, UpdateUservalues).ToArray());

                        // h.ExecuteNonQuery(string.Format("UPDATE TB_User SET  IsUsing = {0} WHERE  UserID = '{1}'", user.IsUsing ? 1 : 0, user.UserID));
                    }
                    else
                    {
                        UpdateUserparasnames.Add("@in_UserName");
                        UpdateUservalues.Add(string.IsNullOrEmpty(user.UserName) ? "" : user.UserName.Trim());
                        h.ExecuteNonQuery(CommandType.Text, sqlUpdateUserName, HZ.Data.DBParameterFactory.GetIDbDataParameterList(UpdateUserparasnames, UpdateUservalues).ToArray());

                        // h.ExecuteNonQuery( string.Format("UPDATE TB_User SET  UserName = '{0}'  WHERE  UserID = '{1}'", user.UserName, user.UserID));
                    }

                }
                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户信息用户不成功", ex);
                retValue = 0;
            }
            return retValue;

        }


        /// <summary>
        /// 用户锁定/解锁（批量）(0,失败;1,成功;)
        /// </summary>
        /// <param name="userids">用户编号集合</param>
        /// <param name="IsUsing">0.锁定;1.解锁</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int LockOrUnblockUser(string userids, int IsUsing)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //用户编号集合(多个用,分隔)
                paras[0] = new SqlParameter("@in_UserIDs", string.IsNullOrEmpty(userids) ? "" : userids);
                //是否启用
                paras[1] = new SqlParameter("@in_IsUsing", IsUsing);

                //输入参数处理结果(0,失败;1,成功;)
                paras[paras.Length - 1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_LockOrUnblock", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "用户锁定/解锁（批量）不成功", ex);

            }

            return retValue;
            */
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string[] UserIdArray = userids.Split(',');
                for (int i = 0; i < UserIdArray.Length; i++)
                {
                    //UpdateUservalues.Add(string.IsNullOrEmpty(user.UserID) ? "" : user.UserID.Trim());
                    if (IsUsing == 1)
                    {
                        h.ExecuteNonQuery(string.Format(@" UPDATE TB_User SET IsUsing = 1 WHERE  UserID ='{0}'", UserIdArray[i]));
                    }
                    else
                    {
                        h.ExecuteNonQuery(string.Format(@"UPDATE TB_User SET  IsUsing =0 WHERE UserID like '{0}%'", UserIdArray[i]));
                    }
                }
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "用户锁定/解锁（批量）不成功", ex);
                Flag = 0;
            }
            return Flag;

        }


        /// <summary>
        /// 初始化用户密码（批量）(0,失败;1,成功;)
        /// </summary>
        /// <param name="userids">用户编号集合</param>
        /// <param name="userPassword">用户密码</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int ResetUserPassword(string userids, string userPassword)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //用户编号集合(多个用,分隔)
                paras[0] = new SqlParameter("@in_UserIDs", string.IsNullOrEmpty(userids) ? "" : userids);
                //是否启用
                paras[1] = new SqlParameter("@in_UserPassword", userPassword);

                //输入参数处理结果(0,失败;1,成功;)
                paras[paras.Length - 1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserPassword_Reset", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "初始化用户密码不成功", ex);

            }

            return retValue;
             */
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string[] UserArray = userids.Split(',');
                string sql = @"UPDATE TB_User SET UserPassword = @in_UserPassword WHERE  UserID = @in_UserID";
                List<string> parasnames = new List<string>();
                List<object> values = new List<object>();
                parasnames.Add("@in_UserPassword");
                parasnames.Add("@in_UserID");
                values.Add(userPassword);
                for (int i = 0; i < UserArray.Length; i++)
                {
                    values.Add(UserArray[i]);
                    h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray()).ToString();
                }
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "初始化用户密码不成功", ex);
                Flag = 0;
            }
            return Flag;
        }


        /// <summary>
        /// 修改密码(0,更新失败;1,更新成功;2,原密码错误)
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="oldpassword">原始密码</param>
        /// <param name="newpassword">新密码</param>
        /// <param name="falg">0.初始化密码1.更新密码</param>
        /// <returns>0,更新失败;1,更新成功;2,原密码错误</returns>
        public static int UpdatePassword(string userid, string oldpassword, string newpassword, int falg)
        {
            #region 前
            //int retValue = 0;
            //try
            //{
            //    SqlParameter[] paras = new SqlParameter[5];

            //    //TB_User【UserID,UserPassword】
            //    //用户编号
            //    paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

            //    //用户名称
            //    paras[1] = new SqlParameter("@in_oldUserPassword", string.IsNullOrEmpty(oldpassword) ? "" : oldpassword);

            //    //是否启用
            //    paras[2] = new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(newpassword) ? "" : newpassword);

            //    //是否启用
            //    paras[3] = new SqlParameter("@in_Flag", falg);

            //    //输入参数处理结果(0,原密码错误;1,更新成功;2,更新失败)
            //    paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

            //    paras[4].Direction = ParameterDirection.Output;


            //    //执行存储过程
            //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserPassword_Update", paras);

            //    retValue = int.Parse(paras[4].Value.ToString());
            //}
            //catch (Exception ex)
            //{
            //    //执行不成功，记录日志 
            //    log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户密码不成功", ex);

            //}

            //return retValue; 
            #endregion

            int retValue = 0;
            User user = GetUserByUserID(userid);

            if (oldpassword != user.UserPassword)
            { return 2; }
            HZ.Data.IDbHelper h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                List<string> UpdatePasswordparasnames = new List<string>();
                List<object> UpdatePasswordvalues = new List<object>();

                UpdatePasswordparasnames.Add("@in_UserID");
                UpdatePasswordvalues.Add(string.IsNullOrEmpty(user.UserID) ? "" : user.UserID.Trim());

                //0.初始化密码1.更新密码
                if (falg == 1)
                {
                    UpdatePasswordparasnames.Add("@in_UserPassword");
                    UpdatePasswordvalues.Add(string.IsNullOrEmpty(newpassword) ? "" : newpassword);
                }
                else if (falg == 0)
                {
                    UpdatePasswordparasnames.Add("@in_UserPassword");
                    UpdatePasswordvalues.Add(HZ.Fun2.GetMd5("999999"));
                }

                string sqlUpdatePassword = @" UPDATE  TB_User 
	                                                SET     UserPassword  = @in_UserPassword
	                                                WHERE   UserID  = @in_UserID";

                h.ExecuteNonQuery(CommandType.Text, sqlUpdatePassword, HZ.Data.DBParameterFactory.GetIDbDataParameterList(UpdatePasswordparasnames, UpdatePasswordvalues).ToArray());


                h.CommitTrans();

                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户密码不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 用户解锁
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static bool UnblockUser(string userid)
        {
            User user = new User();
            user.UserID = userid;
            user.IsUsing = true;
            return UpdateUser(user, 1) == 1;
        }

        /// <summary>
        /// 用户锁定
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static bool LockUser(string userid)
        {
            User user = new User();
            user.UserID = userid;
            user.IsUsing = false;
            return UpdateUser(user, 1) == 1;
        }


        /// <summary>
        /// 更新用户信息[名称、是否启用]同时设置权限(0,更新失败;1,更新成功;2,用户编号不存在;)
        /// </summary>
        /// <param name="user">用户[名称、是否启用、权限集合]</param>
        /// <returns>(0,更新失败;1,更新成功;2,用户编号不存在;)</returns>
        public static int UpdateUserRule(User user)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[5];

                //TB_User【UserID,UserName,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID);
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName);
                //是否启用
                paras[2] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (user.OperatingRuleList != null)
                {
                    if (user.OperatingRuleList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in user.OperatingRuleList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //
                paras[3] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[4].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRule_Update", paras);

                retValue = int.Parse(paras[4].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户信息用户不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 删除用户(0,删除失败;1,删除成功;2,该用户不存在;)
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns>(0,删除失败;1,删除成功;2,该用户不存在;)</returns>
        public static int DeleteUser(string userid)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[2];

                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

                paras[1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[1].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_Del", paras);

                retValue = int.Parse(paras[1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "删除用户不成功", ex);
            }

            return retValue;
            */
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string IsExitSql = string.Format("SELECT COUNT(*) FROM TB_User WHERE  UserID = '{0}'", userid);
                if (int.Parse(h.ExecuteScalar(IsExitSql).ToString()) > 0)
                {

                    string DelRoleUser = @" DELETE FROM   TB_Role_User WHERE  UserID = @in_UserID";
                    string DelUserRule = @" DELETE   FROM   TB_UserRule WHERE  UserID = @in_UserID";
                    string DelUser = @" DELETE FROM   TB_User WHERE  UserID = @in_UserID";
                    h.ExecuteNonQuery(CommandType.Text, DelRoleUser, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_UserID", userid));
                    h.ExecuteNonQuery(CommandType.Text, DelUserRule, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_UserID", userid));
                    h.ExecuteNonQuery(CommandType.Text, DelUser, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_UserID", userid));
                }
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "删除用户不成功", ex);
                Flag = 0;
            }
            return Flag;
        }

        /// <summary>
        /// 获取用户的操作权限
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns>操作权限集合</returns>
        public static IList<OperatingRule> GetUserOperatingRule(string userid)
        {
            string sql_temp = @"SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  View_UserRule WHERE  UserID='{0}' order by OperatingRuleID";

            StringBuilder sbWhere = new StringBuilder("");

            if (!string.IsNullOrEmpty(userid))
            {
                sbWhere.AppendFormat(sql_temp, userid); // 增加UserID查询条件                
            }

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString());
            list = DAL2.DAL_Helper.GetOperatingRulesFromDataTable(dt);
            //list = HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(dt);
            return list;
        }


        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<OperatingRule> GetAllOperatingRule()
        {
            string sql_temp = @"SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  TB_OperatingRule ";

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_temp);
            //list = HZ.Data.DAL_Helper.GetOperatingRulesFromDataTable(dt);
            list = HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(dt);
            return list;
        }


        /// <summary>
        /// 根据 模块名 操作名 获取权限
        /// </summary>
        /// <param name="Module">模块名</param>
        /// <param name="OperatingRuleName">操作名</param>
        /// <returns>权限</returns>
        public static OperatingRule GetOperatingRule(HZ.DrugTrade.Model.Enum.Module Module, HZ.DrugTrade.Model.Enum.OperateType OperateType)
        {
            string sql_temp = @" SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  TB_OperatingRule where OperatingRuleName='{0}' and Module='{1}'";

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, OperateType.ToString(), Module.ToString()));
            //list = HZ.Data.DAL_Helper.GetOperatingRulesFromDataTable(dt);
            list = HZ.Data.DAL_Helper.CommonFillList<OperatingRule>(dt);
            return list.Count > 0 ? list[0] : new OperatingRule();
        }

        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<HZ.DrugTrade.Model.View.UserMenuRule> GetUserMenuRule(string userID)
        {
            string sql_temp = @"SELECT  *  FROM  View_UserMenuRule  WHERE  Upper(UserID) ='{0}' 
	                                       AND RoleType = (SELECT RoleID 
	                                       FROM TB_Role_User  
	                                       WHERE  Upper(UserID) ='{1}')";

            IList<HZ.DrugTrade.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, userID.ToUpper(), userID.ToUpper()));
            list = HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.UserMenuRule>(dt);
            return list;
        }

        /// <summary>
        /// 判断某个用户是否有该权限
        /// </summary>
        /// <param name="userID">用户编号</param>
        /// <param name="module">权限模块</param>
        /// <param name="OperatingRuleID">权限ID</param>
        /// <returns></returns>
        public static bool CheckUserRule(string userID, HZ.DrugTrade.Model.Enum.Module module, HZ.DrugTrade.Model.Enum.OperateType operateType)
        {
            string sql_temp = @"SELECT  *  FROM  View_UserRule WHERE  UserID ='{0}' AND Module='{1}' AND OperatingRuleName='{2}' ";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, userID, HZ.DrugTrade.Model.Enum.EnumParse.GetModuleName(module), HZ.DrugTrade.Model.Enum.EnumParse.GetOperateName(operateType)));

            return dt.Rows.Count > 0;
        }

        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<HZ.DrugTrade.Model.View.UserMenuRule> GetAllUserMenuRule()
        {
            string sql_temp = @"SELECT  DISTINCT 
	                                        '' AS OperatingRuleID,
                                            MenuID,
                                            ParentMenuID,
	                                        MenuName,
	                                        WinPath,
	                                        WebPath,
                                            IcoPath,
	                                        Sort,
                                            RoleType,
	                                        '' as UserID,
	                                        1	as IsEffective  FROM  TB_Menu ";

            IList<HZ.DrugTrade.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_temp);
            list = HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.UserMenuRule>(dt);
            return list;
        }


        /// <summary>
        /// 获取某个单位所有操作权限菜单
        /// </summary>
        /// <param name="RoleType">菜单所属角色：03.管理机构 02.配送企业01.医疗机构</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.UserMenuRule> GetAllUserMenuRule(string RoleType)
        {
            string sql_temp = @"SELECT  DISTINCT 
	                                        '' AS OperatingRuleID,
                                            MenuID,
                                            ParentMenuID,
	                                        MenuName,
	                                        WinPath,
	                                        WebPath,
                                            IcoPath,
	                                        Sort,
                                            RoleType,
	                                        '' as UserID,
	                                        1	as IsEffective  FROM  TB_Menu WHERE RoleType ='{0}'";

            IList<HZ.DrugTrade.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, RoleType));
            list = HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.UserMenuRule>(dt);
            return list;
        }

        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<User> GetUserByWhere(HZ.DrugTrade.Model.Query.User user, int currentPage, int pageSize, out int totalRecord)
        {
            string sql_temp = @"SELECT * FROM View_UserRole WHERE 1=1 ";

            StringBuilder sbWhere = new StringBuilder(sql_temp);

            if (user != null)
            {
                if (!string.IsNullOrEmpty(user.Userid))
                {
                    sbWhere.AppendFormat(" AND  UserID = '{0}'", user.Userid.Trim()); // 增加UserID查询条件
                }
                if (!string.IsNullOrEmpty(user.Username))
                {
                    sbWhere.AppendFormat(" AND  UserName LIKE '%{0}%'", user.Username.Trim()); // 增加UserName查询条件
                }
                if (!string.IsNullOrEmpty(user.OrgID))
                {
                    sbWhere.AppendFormat(" AND  OrgID = '{0}'", user.OrgID.Trim()); // 增加OrgID查询条件
                }
                if (!string.IsNullOrEmpty(user.RoleID))
                {
                    sbWhere.AppendFormat(" AND  RoleID = '{0}'", user.RoleID.Trim()); // 增加RoleID查询条件
                }
                if (user.IsAdmin != null)
                {
                    sbWhere.AppendFormat(" AND  IsUserAdmin = {0}", (bool)user.IsAdmin ? 1 : 0); // 增加IsUserAdmin是否主用户查询条件
                }
                if (user.Isusing != null)
                {
                    sbWhere.AppendFormat(" AND  Isusing = {0}", (bool)user.Isusing ? 1 : 0); // 增加Isusing查询条件
                }
                if (user.Isonline != null)
                {
                    sbWhere.AppendFormat(" AND  Isonline =  {0}", (bool)user.Isonline ? 1 : 0); // 增加Isonline查询条件
                }
                if (user.LastlogintimeFrom != null)
                {
                    sbWhere.AppendFormat(" AND  Lastlogintime >= '{0}'", user.LastlogintimeFrom.ToString()); // 增加LastlogintimeFrom查询条件
                }

                if (user.LastlogintimeTo != null)
                {
                    sbWhere.AppendFormat(" AND  Lastlogintime <= '{0}'", user.LastlogintimeTo.ToString());  // 增加LastlogintimeTo查询条件
                }

            }

            sbWhere.Append(" ORDER BY UserID ");
            IList<User> list;
            totalRecord = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString()).Rows.Count;
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sbWhere.ToString(), pageSize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            //list = HZ.Data.DAL_Helper.GetUsersFromDataTable(dt);
            list = HZ.Data.DAL_Helper.CommonFillList<User>(dt);
            return list;
        }


        /// <summary>
        /// 条件机构用户查询（含机构角色相关信息）
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.OrgUsers> GetOrgUsersByWhere(HZ.DrugTrade.Model.Query.OrgUsers user, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.OrgUsers>(user, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_OrgUsers WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.OrgUsers>(dt);
        }


        /// <summary>
        /// 条件医疗机构用户查询（含机构角色相关信息）
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalUsers> GetHospitalUsersByWhere(HZ.DrugTrade.Model.Query.HospitalUsers user, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.HospitalUsers>(user, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM view_orgusers WHERE 1=1 and userid not like 'A%' and userid not like 'S%' and userid not like 'P%' {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalUsers>(dt);
        }

        /// <summary>
        /// 条件医疗机构入围授权查询
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalCatalogManageRule> GetHospitalCatalogManageRulesByWhere(HZ.DrugTrade.Model.Query.HospitalCatalogManageRule query, int currentPage, int pageSize, out int totalRecord)
        {
            //string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.HospitalCatalogManageRule>(query);

            string where = "";
            //医疗机构名称
            string HospitalName = query.HospitalName;

            //地区编号
            string AreaID = "";
            if (!string.IsNullOrEmpty(query.AreaID))
            {
                //省：32江苏省；市：3201南京市；县区：320103白下区
                AreaID = MyString.TrimEnd(query.AreaID, "00");
            }


            if (HospitalName.Trim() != "")
            {
                where = string.Format(" AND HospitalName like  '%{0}%' ", HospitalName);
            }

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT * FROM (SELECT DISTINCT 
      TB_Hospital.HospitalName, TB_Hospital.AreaID, TB_Area.AreaName, 
      TB_UserRule.IsEffective, TB_Area.FatherAreaID, TB_Hospital.HospitalID, 
      TB_User.UserID, TB_UserRule.OperatingRuleID
      FROM TB_User INNER JOIN
      TB_Role_User ON TB_User.UserID = TB_Role_User.UserID AND 
      TB_Role_User.IsAdmin = 1 INNER JOIN
      TB_Role ON TB_Role_User.RoleID = TB_Role.RoleID INNER JOIN
      TB_Hospital ON TB_Role_User.OrgID = TB_Hospital.HospitalID AND 
      TB_Role.RoleID = '01' INNER JOIN
      TB_Area ON TB_Hospital.AreaID = TB_Area.AreaID LEFT OUTER JOIN
      TB_UserRule ON TB_User.UserID = TB_UserRule.UserID AND 
      TB_UserRule.OperatingRuleID = '{0}') TT WHERE 1=1  AND AreaID LIKE  '{1}%'  {2}  ORDER BY AreaID,HospitalID", query.OperatingRuleID, AreaID, where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalCatalogManageRule>(dt);
        }

        /// <summary>
        /// 添加用户同时设置权限(0,添加失败;1,添加成功;2,用户编号已存在;)
        /// </summary>
        /// <param name="user">用户对象（每个用户的UserRole仅RoleID须赋值，Org仅OrgID须赋值）</param>
        /// <param name="ruleIList">操作权限集合（每个权限仅RuleID须赋值）</param>
        /// <returns>(0,添加失败;1,添加成功;2,用户编号已存在;)</returns>
        public static int AddUser_OperatingRule(User user, IList<OperatingRule> ruleIList)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[11];

                //TB_User【UserID,UserName,UserPassword,IsAdmin,IsOnline,IsOnline,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID.Trim());
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName.Trim());
                //密码
                paras[2] = new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(user.UserPassword) ? "" : user.UserPassword.Trim());
                //是否管理员
                paras[3] = new SqlParameter("@in_IsAdmin", user.IsAdmin ? 1 : 0);
                //是否在线
                paras[4] = new SqlParameter("@in_IsOnline", user.IsOnline ? 1 : 0);
                //是否启用
                paras[5] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);

                //TB_Role_User【UserID,RoleID,IsAdmin,OrgID】
                if (user.UserRole != null)
                {
                    if (string.IsNullOrEmpty(user.UserRole.RoleID) || string.IsNullOrEmpty(user.Org.OrgID))
                    {
                        new Exception("用户角色相关信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                    }

                    //角色编号
                    paras[6] = new SqlParameter("@in_RoleID", string.IsNullOrEmpty(user.UserRole.RoleID) ? "" : user.UserRole.RoleID.Trim());
                    //是否主用户
                    paras[7] = new SqlParameter("@in_IsUserAdmin", user.IsUserAdmin ? 1 : 0);
                    //机构编号
                    paras[8] = new SqlParameter("@in_OrgID", string.IsNullOrEmpty(user.Org.OrgID) ? "" : user.Org.OrgID.Trim());
                }
                else
                {
                    new Exception("用户角色信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                }

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (ruleIList != null)
                {
                    if (ruleIList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in ruleIList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //权限编号集合
                paras[9] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[10] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[10].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRoleRule_Add", paras);

                retValue = int.Parse(paras[10].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "添加用户并设置权限不成功", ex);
            }
            return retValue;
            */
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string IsExitSql = string.Format(" SELECT count(*) FROM TB_User WHERE  UserID = '{0}'", user.UserID);

                if (int.Parse(h.ExecuteScalar(IsExitSql).ToString()) == 0)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append(" begin ");
                    //插入用户表
                    sb.Append(string.Format(@"  INSERT INTO TB_User(UserID, UserName,UserPassword, IsAdmin,IsUsing, IsOnline)
	VALUES('{0}','{1}','{2}',{3},{4},{5}) ;", user.UserID, user.UserName, user.UserPassword, user.IsAdmin ? 1 : 0, user.IsUsing ? 1 : 0, user.IsOnline ? 1 : 0));
                    //插入用户角色表
                    sb.Append(string.Format("INSERT INTO TB_Role_User(UserID, RoleID,IsAdmin,OrgID) VALUES ('{0}','{1}',{2},'{3}') ;", user.UserID, user.UserRole.RoleID, user.IsUserAdmin ? 1 : 0, user.Org.OrgID));
                    if (ruleIList != null && ruleIList.Count > 0)
                    {
                        foreach (var item in ruleIList)
                        {
                            //批量添加角色权限
                            sb.Append(string.Format("INSERT INTO TB_UserRule(UserID,OperatingRuleID,IsEffective)VALUES('{0}','{1}',1) ;", user.UserID, item.OperatingRuleID));
                        }
                    }
                    sb.Append(" end; ");
                    h.ExecuteNonQuery(sb.ToString());
                    Flag = 1;
                }
                else
                {
                    Flag = 2;                  
                  
                }
                h.CommitTrans();
               
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "添加用户并设置权限不成功", ex);
                Flag = 0;
            }
            return Flag;

        }

        /// <summary>
        /// 设置用户的操作权限
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="ruleIList">操作权限集合</param>
        /// <returns></returns>
        public static bool SetUserOperatingRule(string userid, IList<OperatingRule> ruleIList)
        {
            /*
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //如果用户编号为空
                if (string.IsNullOrEmpty(userid))
                {
                    new Exception("用户角色信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                }
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid.Trim());

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (ruleIList != null)
                {
                    if (ruleIList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in ruleIList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //
                paras[1] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                paras[2] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[2].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRule_Add", paras);

                retValue = int.Parse(paras[2].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "设置用户权限不成功", ex);
            }

            return retValue == 1 ? true : false;
             */
            /*
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                StringBuilder sb = new StringBuilder();
                //先查有没有这个用户，如果有再执行
                //先 删除用户所有权限
                //然后 批量添加用户权限
                // 如果是主用户 同步子用户的权限：移除原来主用户有，现在没有了的权限
                 
                sb.AppendLine( string.Format(@"declare  V_Count Number;
                           V_IsAdmin Number;
                            begin 
                              V_Count:=0;
                              SELECT count(*) into V_Count FROM  TB_User WHERE UserID ='{0}';
                              if V_Count >0 then 
                                V_IsAdmin:=0;
                                SELECT  IsAdmin into V_IsAdmin FROM TB_User WHERE UserID = '{0}';
                                DELETE FROM TB_UserRule WHERE UserID = '{0}';", userid));
                foreach (var item in ruleIList)
                {
                    sb.AppendLine(string.Format(@" INSERT INTO TB_UserRule (UserID,OperatingRuleID,IsEffective) VALUES ('{0}','{1}',1);", userid, item.OperatingRuleID));
                }
                sb.AppendLine(string.Format(@"  if V_IsAdmin =1 then 
                                     DELETE  FROM TB_UserRule
                                         WHERE   OperatingRuleID NOT IN (SELECT  OperatingRuleID FROM TB_UserRule WHERE UserID = '' )
                                             AND UserID IN (SELECT UserID FROM  TB_Role_User WHERE   OrgID ='{0}' AND IsAdmin = 0 );                         
                                   end if;
                              end if;  
                          end; ", userid));
                string sqlStr = sb.ToString();
                h.ExecuteNonQuery(sb.ToString());
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "设置用户权限不成功", ex);
                Flag = 0;
            }
            return Flag == 1 ? true : false;
             * */
            if (ruleIList == null && ruleIList.Count == 0)
            {
                return true;
            }
            bool Flag = false;
            Oracle.DataAccess.Client.OracleConnection connection = DAL_OracleHelper.OracleConnection();
            Oracle.DataAccess.Client.OracleCommand cmd = DAL_OracleHelper.OracleCommand();
            try
            {
                //填充表参数
                TBOPERATINGRULEID dt = new TBOPERATINGRULEID();
                List<OPERATERULEID> NewList = new List<OPERATERULEID>();
                for (int i = 0; i < ruleIList.Count; i++)
                {
                    OPERATERULEID newobj = new OPERATERULEID();
                    newobj.OPERATINGRULEID = ruleIList[i].OperatingRuleID;
                    NewList.Add(newobj);
                }

                dt.Value = NewList.ToArray();
                //打开连接
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "usp_UserRule_Add";//存储过程名字
                //DistributionList, string hospitalID, string hospitalName, string hospitalAreaID)
                Oracle.DataAccess.Client.OracleParameter[] pars = new Oracle.DataAccess.Client.OracleParameter[5];
                pars[0] = new Oracle.DataAccess.Client.OracleParameter("V_UserID", userid);
                pars[1] = new Oracle.DataAccess.Client.OracleParameter("v_TBOPERATINGRULEID", Oracle.DataAccess.Client.OracleDbType.Object);
                pars[1].UdtTypeName = "TBOPERATINGRULEID";
                pars[1].Value = dt;
                pars[2] = new Oracle.DataAccess.Client.OracleParameter("out_success", Oracle.DataAccess.Client.OracleDbType.Int32);
                pars[2].Direction = ParameterDirection.Output;
                cmd.Parameters.Add(pars[0]);
                cmd.Parameters.Add(pars[1]);
                cmd.Parameters.Add(pars[2]);
                cmd.ExecuteNonQuery();
                Flag = int.Parse(pars[2].Value.ToString()) == 1 ? true : false;

            }
            catch (Exception e)
            {
                Flag = false;
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "设置用户权限不成功", e);
        
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
                connection.Dispose();
            }
            return Flag;

        }

        /// <summary>
        /// 授权(0,失败;1,成功;)
        /// </summary>
        /// <param name="Orgs">机构字典(key:OrgID,value:RoleID)</param>
        /// <param name="flag">启用情况：0未启用；1启用</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int AuthorizeOrg(IDictionary<string, string> Orgs, int flag)
        {
            int retValue = 0;

            try
            {
                SqlParameter[] paras = new SqlParameter[4];

                //处理机构编号
                StringBuilder sbOrgIDs = new StringBuilder("");
                string strOrgIDs = "";
                //处理角色编号
                StringBuilder sbRoleIDs = new StringBuilder("");
                string strRoleIDs = "";
                if (Orgs.Count > 0)
                {

                    foreach (var OrgID in Orgs)
                    {
                        sbOrgIDs.Append(OrgID.Key + ",");
                    }
                    strOrgIDs = sbOrgIDs.ToString().Trim().Substring(0, sbOrgIDs.Length - 1);

                    foreach (var OrgID in Orgs)
                    {
                        sbRoleIDs.Append(OrgID.Value + ",");
                    }

                    strRoleIDs = sbRoleIDs.ToString().Trim().Substring(0, sbRoleIDs.Length - 1);

                }

                //权限编号集合
                paras[0] = new SqlParameter("@in_OrgIDs", strOrgIDs);
                //角色编号集合
                paras[1] = new SqlParameter("@in_RoleIDs", strRoleIDs);
                //启用情况：0未启用；1启用
                paras[2] = new SqlParameter("@in_IsUsing", flag);

                //输入参数处理结果(0,添加失败;1,添加成功;)
                paras[3] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_AuthorizeOrg", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "机构授权操作不成功", ex);
            }

            return retValue;
        }


        /// <summary>
        /// 授权医疗机构入围(0,失败;1,成功;)
        /// </summary>
        /// <param name="userIDs">用户编号集合（如：1,2,3）</param>
        /// <param name="flag">启用情况：0未授权；1授权</param>
        /// <param name="operatingRuleID">操作编号</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int AuthorizeHospitalCatalogManageRule(string userIDs, int flag, string operatingRuleID)
        {
            //int retValue = 0;
            //try
            //{
            //    SqlParameter[] paras = new SqlParameter[]
            //    {
            //      new SqlParameter("@in_UserIDs", userIDs),
            //      new SqlParameter("@in_IsEffective", flag),
            //      new SqlParameter("@in_OperatingRuleID", operatingRuleID),
            //      new SqlParameter("@out_success", SqlDbType.Int)
            //    };
            //    paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //    //执行存储过程
            //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_AuthorizeHospitalCatalogManageRule", paras);
            //    retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            //}
            //catch (Exception ex)
            //{
            //    //执行不成功，记录日志 
            //    log4netManager.LogDebug(typeof(DAL_User).FullName, "医疗机构入围操作不成功", ex);
            //}

            //return retValue;

            string[] arrayUserID = userIDs.Split(',');

            #region updated by sinodier 2011年1月15日15:03:10
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                if (arrayUserID.Length == 0)
                {
                    return 0;
                }
                for (var i = 0; i < arrayUserID.Length; i++)
                {
                    var item = arrayUserID[i];


                    /*
                     SELECT  count(1) num  FROM   TB_UserRule WHERE  UserID = @in_UserID AND OperatingRuleID = @in_OperatingRuleID
                     */
                    string strCheck = @"SELECT  count(1) num  FROM   TB_UserRule WHERE  UserID = @in_UserID AND OperatingRuleID = @in_OperatingRuleID";

                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();

                    parasnames.Add("@in_UserID");
                    values.Add(string.IsNullOrEmpty(item) ? "" : item.Trim());

                    parasnames.Add("@in_OperatingRuleID");
                    values.Add(string.IsNullOrEmpty(operatingRuleID) ? "" : operatingRuleID.Trim());

                    DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());

                    int num = int.Parse(dt.Rows[0]["num"].ToString());

                    /*
                         * 新增用户权限        
                         */
                    string sqlInsertUserRule = string.Format(@" INSERT INTO TB_UserRule
			              (
			                UserID,
			                OperatingRuleID,
			                IsEffective
			              )
			            VALUES
			              (
			                @in_UserID,
			                @in_OperatingRuleID,
			                1
			              )");

                    //新增用户权限 
                    if (num == 0 && flag == 1)
                    {
                        #region 新增用户权限
                        h.ExecuteNonQuery(CommandType.Text, sqlInsertUserRule, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                        #endregion
                    }
                    else if (num > 0 && flag == 0)
                    {

                        #region 删除权限
                        /*
                         * 删除权限        
                         */
                        string sqlDelUserRule = @"DELETE  FROM  TB_UserRule  WHERE  UserID = @in_UserID AND OperatingRuleID = @in_OperatingRuleID";
                        h.ExecuteNonQuery(CommandType.Text, sqlDelUserRule, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                        #endregion
                    }

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("AuthorizeHospitalCatalogManageRule", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion


        }


        public static string GetAdminUserID(string p)
        {
            string sql = string.Format(@"SELECT  tru.UserID FROM TB_User AS tu INNER JOIN  TB_Role_User AS tru
ON tu.UserID = tru.UserID WHERE tru.IsAdmin =1 AND tu.UserID = '{0}'", p);
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString();

        }
    }
}

